package com.scalar.chiptrack.rma.dao;

import java.util.Calendar;
import java.util.Date;
import java.util.ArrayList;
import java.util.Iterator;

import java.sql.*;
import com.scalar.chiptrack.utils.StringUtils;
import com.scalar.chiptrack.ChipTrackActionForm;
import com.scalar.chiptrack.db.DBTransaction;
import com.scalar.chiptrack.db.DataSource;
import com.scalar.chiptrack.rma.forms.RMAForm;
import com.scalar.chiptrack.rma.RMALineItem;
import com.scalar.ScalarException;


public class RmaDAO{

	public static int saveRMADetails(DBTransaction dbTransaction, ChipTrackActionForm form){

		Connection conn = null;
		ResultSet rs = null;
		PreparedStatement ps = null;
		int rmaId = -1;
		boolean success = false;

		RMAForm rmaForm = (RMAForm)form;

	try{
		if(dbTransaction == null){
			conn = DataSource.getConnection();
		}else{
			conn = dbTransaction.getConnection();
		}

		ps = conn.prepareStatement("insert into RMA_Table(RMA_Number, Customer_ID, Customer_Name, RMA_Contact_Person, RMA_Requested_Date, RMA_Issue_Date, RMA_Expiration_Date, RMA_Billing_Address_Id, RMA_Status, Address1, Address2, Address3, City, State, Zip, Country, Phone, Fax, Email, Created_On, Updated_On , Address4 , End_User_ID, RMA_VENDOR_ID, RMA_VENDOR_ADDRESS ) values ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");

        ps.setString(1, (rmaForm.getRmaNumber()).trim());
		ps.setString(2, rmaForm.getCustomerId()+"");
		ps.setString(3, rmaForm.getCustomerName());
		ps.setString(4, rmaForm.getContactPerson());
		ps.setDate(5, StringUtils.getSQLDate(rmaForm.getRmaRequestDate(),null));
		ps.setDate(6, StringUtils.getSQLDate(rmaForm.getRmaIssueDate(),null));
		ps.setDate(7, StringUtils.getSQLDate(rmaForm.getRmaExpirationDate(),null));
		ps.setString(8, rmaForm.getBillingAddressId()+"");
		ps.setString(9, "ISSUED");
		ps.setString(10, rmaForm.getAddress1());
		ps.setString(11, rmaForm.getAddress2());
		ps.setString(12, rmaForm.getAddress3());
		ps.setString(13, rmaForm.getCity());
		ps.setString(14, rmaForm.getState());
		ps.setString(15, rmaForm.getZipcode());
		ps.setString(16, rmaForm.getCountry());
		ps.setString(17, rmaForm.getPhoneNumber());
		ps.setString(18, rmaForm.getFaxNumber());
		ps.setString(19, rmaForm.getEmail());

		Date date = Calendar.getInstance().getTime();

		ps.setString(20, StringUtils.formatDateToString ( date , "MM/dd/yyyy" ));
		ps.setString(21, StringUtils.formatDateToString ( date , "MM/dd/yyyy" ));

        ps.setString(22, rmaForm.getAddress4());
        ps.setString(23, rmaForm.getEndUserID() > 0 ? rmaForm.getEndUserID()+"" : null );
        ps.setInt(24, rmaForm.getVendorId());
        ps.setString(25, rmaForm.getVendorAddressString() );

        ps.executeUpdate();
		ps.close();
		ps = null;

		ps = conn.prepareStatement("select @@IDENTITY as Rma_Id");

		rs = ps.executeQuery();

		if(rs.next()){
			 rmaId = rs.getInt("Rma_Id");
		}
		 success = true;
	}
	catch (Exception se) {
		// Exception while updating user profile
		// log it
		se.printStackTrace();
	}
	finally {
	    if (dbTransaction == null) {
		    try {
		    	if (conn != null) {
		        	if (success == true)
		            	conn.commit();
		            else
		            	conn.rollback();
		       	}
		    }
		    catch (SQLException ignore) {}
		 }

		 if (ps != null) {
		 	try { ps.close(); } catch (SQLException ignore){}
		 }

		 if (conn != null) {
		 	try { conn.close(); } catch (SQLException ignore){}
		 }
    }

		return rmaId;
	}


    private static String loadSearchString( String rmaNumber, String customerId, String status ) throws ScalarException
    {

       String searchString = "";

       if( ( rmaNumber != null ) && ( rmaNumber.trim().length() != 0 ) )
       {
            searchString = searchString + " and RMA_Table.RMA_Number like '"+ StringUtils.formatToHandleQuote( rmaNumber.trim() ) +"%'";
       }

       if( ( customerId != null ) && ( !"0".equals( customerId ) && ( customerId.length() > 0 ) ) )
       {
           searchString = searchString +  " and Customer_ID = " + Integer.parseInt(customerId);
       }

       if( ( status != null ) && ( !"All".equals( status ) ) )
       {
           searchString = searchString + " and RMA_Status = '" + status + "'";
       }

       if ( !"".equals( searchString ) )
       {
           searchString = searchString.substring( 4, searchString.length() );
           searchString = "where" + searchString ;
       }

       return searchString ;

   }

   public static ArrayList searchRmaDetails( String rmaNumber, String customerId, String status )
   {

		Connection conn     = null;
		Statement st        = null;
		ResultSet rs        = null;

		ArrayList list      = new ArrayList();
		boolean success     = false;
		try
        {
			conn = DataSource.getConnection();

			st = conn.createStatement();
            rs = st.executeQuery( "select RMA_ID, RMA_Number, Customer_Name, RMA_Requested_Date, RMA_Issue_Date, RMA_Status  from RMA_Table " + loadSearchString( rmaNumber , customerId , status ) + "  order by RMA_Table.RMA_ISSUE_DATE, RMA_Table.RMA_Number" );

			while( rs.next() )
            {
				RMAForm form = new RMAForm();

				form.setRmaId( rs.getInt( "RMA_ID" ) );
				form.setRmaNumber( rs.getString( "RMA_Number" ) );
				form.setCustomerName( rs.getString( "Customer_Name" ) );
				form.setRmaRequestDate( StringUtils.formatDateToString( rs.getDate( "RMA_Requested_Date" ) ) );
				form.setRmaIssueDate( StringUtils.formatDateToString( rs.getDate( "RMA_Issue_Date" ) ) );
				form.setRmaStatus( rs.getString( "RMA_Status" ) );

				list.add( form );
			}

			success = true;

		}
        catch( Exception e )
        {
			e.printStackTrace();
		}
        finally
        {
			try
            {
				if(success == true){
					conn.commit();
				}else{
					conn.rollback();
				}
			}catch(SQLException sql){ }
            
            if( rs != null )
            {
				try{rs.close();}catch(Exception e){}
			}
            if( st != null )
            {
				try{st.close();}catch(Exception e){}
			}
			if( conn != null )
            {
				try{ conn.close();}catch(Exception e){}
			}
		}

		return list;
	}

	public static ArrayList getRmaDetails(String rmaNumber, String customerId, String status){

		Connection conn = null;
		CallableStatement cs = null;
		ResultSet rs = null;
		ArrayList list = new ArrayList();
		boolean success = false;
		try{

			conn = DataSource.getConnection();

			cs = conn.prepareCall("{call sp_get_rma_details(?, ?, ?)}");
			cs.setString(1, rmaNumber);
			cs.setString(2, customerId);
			cs.setString(3, status);

			rs = cs.executeQuery();

			while(rs.next()){
				RMAForm form = new RMAForm();

				form.setRmaId(rs.getInt("RMA_ID"));
				form.setRmaNumber(rs.getString("RMA_Number"));
				form.setCustomerName(rs.getString("Customer_Name"));
				form.setRmaRequestDate(StringUtils.formatDateToString(rs.getDate("RMA_Requested_Date")));
				form.setRmaIssueDate(StringUtils.formatDateToString(rs.getDate("RMA_Issue_Date")));
				form.setRmaStatus(rs.getString("RMA_Status"));

				list.add(form);
			}

			success = true;

		}catch(Exception e){
			e.printStackTrace();
		}finally{
			try{
				if(success == true){
					conn.commit();
				}else{
					conn.rollback();
				}
			}catch(SQLException sql){
			}
			if(cs != null){
				try{cs.close();}catch(Exception e){}
			}
			if(rs != null){
				try{rs.close();}catch(Exception e){}
			}
			if(conn != null){
				try{ conn.close();}catch(Exception e){}
			}
		}

		return list;
	}

	public static void getRmaDetails(RMAForm form, String rmaId)
    {

		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		boolean success = false;
		try{

            conn = DataSource.getConnection();

			ps = conn.prepareStatement("SELECT RMA_ID, RMA_NUMBER, CUSTOMER_ID , CUSTOMER_NAME, RMA_CONTACT_PERSON, CONVERT(VARCHAR, RMA_REQUESTED_DATE, 101) AS RMA_REQUESTED_DATE, CONVERT(VARCHAR, RMA_ISSUE_DATE, 101) AS RMA_ISSUE_DATE, RMA_BILLING_ADDRESS_ID, RMA_STATUS, ADDRESS1, ADDRESS2, ADDRESS3, ADDRESS4, CITY, STATE, ZIP, COUNTRY, PHONE, FAX, EMAIL, CONVERT(VARCHAR, CLOSE_DATE, 101) AS CLOSE_DATE , COMMENTS, CASE WHEN END_USER_ID IS NULL OR END_USER_ID < 0 THEN '' ELSE ( SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID = END_USER_ID ) END AS  END_USER, END_USER_ID, RMA_VENDOR_ID, RMA_VENDOR_ADDRESS FROM RMA_TABLE WHERE RMA_ID = ?");
            ps.setString(1, rmaId);
            rs = ps.executeQuery();

			if(rs.next()){

                form.setRmaId(rs.getInt("RMA_ID"));
				form.setRmaNumber(rs.getString("RMA_Number"));
                form.setCustomerId(rs.getInt("Customer_ID"));
				form.setCustomerName(rs.getString("Customer_Name"));
				form.setContactPerson(rs.getString("RMA_Contact_Person"));
				form.setRmaRequestDate(rs.getString("RMA_Requested_Date"));
				form.setRmaIssueDate(rs.getString("RMA_Issue_Date"));
				form.setBillingAddressId(rs.getInt("RMA_Billing_Address_Id"));
				form.setRmaStatus(rs.getString("RMA_Status"));
				form.setAddress1(rs.getString("Address1"));
				form.setAddress2(rs.getString("Address2"));
				form.setAddress3(rs.getString("Address3"));
                form.setAddress4(rs.getString("Address4"));
				form.setCity(rs.getString("City"));
				form.setState(rs.getString("State"));
				form.setZipcode(rs.getString("Zip"));
				form.setCountry(rs.getString("Country"));
				form.setPhoneNumber(rs.getString("Phone"));
				form.setFaxNumber(rs.getString("Fax"));
				form.setEmail(rs.getString("Email"));
				form.setCloseDate(rs.getString("Close_Date"));
                form.setComments(rs.getString("Comments"));
                form.setEndUser(rs.getString("END_USER"));
                form.setEndUserID(rs.getInt("End_User_ID"));
                form.setVendorId(rs.getInt("RMA_VENDOR_ID"));
                form.setVendorAddressString(rs.getString("RMA_VENDOR_ADDRESS"));
            }

			success = true;

		}
        catch( Exception e )
        {
			e.printStackTrace();
		}
        finally
        {
			if(rs != null){
				try{rs.close();}catch(Exception e){}
			}
            if(ps != null){
				try{ps.close();}catch(Exception e){}
			}
            if(conn != null){
				try{ conn.close();}catch(Exception e){}
			}
		}
	}

    public static boolean  saveRMALineItemDetails(DBTransaction dbTransaction, ChipTrackActionForm form, ArrayList list){

        ArrayList itemsToInsert = new ArrayList();
        ArrayList itemsToUpdate = new ArrayList();
        ArrayList itemsToCancel = new ArrayList();
        Iterator items = list.iterator();
        boolean hasUpdate = false;
        boolean hasInsert = false;
        boolean hasCancel = false;

        while(items.hasNext()){
                 RMALineItem lineItem = (RMALineItem)items.next();
                 int lineItemId = lineItem.getLineItemId();

                 if( lineItemId == -1 && lineItem.getPartNumber() != null) {
                     itemsToInsert.add(lineItem);
                 }
                 else if( lineItemId != -1 && lineItem.getIsCancel() ){
                     itemsToCancel.add(lineItem);
                 }
                 else {
                     itemsToUpdate.add(lineItem);
                 }
        }

        if( itemsToCancel != null && itemsToCancel.size() > 0)
        {
            hasUpdate= true;
        }
        else if( itemsToInsert != null && itemsToCancel.size() > 0)
        {
            hasUpdate= true;
        }
        hasInsert = insertRMALineItemDetails( dbTransaction,  form, itemsToInsert );
        hasUpdate = updateRMALineItemDetails( dbTransaction,  form, itemsToUpdate );
        hasCancel = cancelRMALineItemDetails( dbTransaction,  form, itemsToCancel );

        closeRMA (dbTransaction,  form);
        if (hasInsert || hasCancel || hasUpdate)
        {
            return true;
        }
        else
        {
            return false;
        }
    }
    public static boolean saveRMAApprovedLineItemDetails(DBTransaction dbTransaction, ArrayList list)
    {
        Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;

		boolean success = false;
		try{

			if(dbTransaction == null){
				conn = DataSource.getConnection();
			}else{
				conn = dbTransaction.getConnection();
			}
            ArrayList itemsToInsert = new ArrayList();
            ArrayList itemsToUpdate = new ArrayList();
            ArrayList itemsToCancel = new ArrayList();

            Iterator items = list.iterator();

            while(items.hasNext())
            {
                 RMALineItem lineItem = ( RMALineItem )items.next();
                 int lineItemId = lineItem.getLineItemId();
                 if (lineItem.getIsApproved())
                 {
                    ps = conn.prepareStatement ( "update RMA_Line_Item_Table set is_approved = ? where RMA_ID = ? and LineItem_Number = ? " );
                    ps.setInt ( 1, lineItem.getIsApproved() == true ? 0 : 1 );
                    ps.setInt ( 2, lineItem.getRmaId() );
                    ps.setInt ( 3, lineItem.getLineItemNo() );
                    ps.executeUpdate();
                    success = true;
                 }
            }

		}catch(Exception e){
			e.printStackTrace();
		}finally{
			if(dbTransaction == null){
				try{
					if(success == true){
						conn.commit();
					}else{
						conn.rollback();
					}
				}catch(SQLException sql){
				}
			}
			if(ps != null){
				try{ps.close();}catch(Exception e){}
			}
			if(rs != null){
				try{rs.close();}catch(Exception e){}
			}
			if(conn != null){
				try{ conn.close();}catch(Exception e){}
			}
		}
        return success;
        }

	public static boolean insertRMALineItemDetails(DBTransaction dbTransaction, ChipTrackActionForm form, ArrayList list){

		Connection conn = null;
		PreparedStatement ps = null;
		boolean success = false;

        String status = null;

		RMAForm rmaForm = (RMAForm)form;
		if((list != null) && list.isEmpty()){
			return false ;
		}

		try{
			if(dbTransaction == null){
				conn = DataSource.getConnection();
			}else{
				conn = dbTransaction.getConnection();
			}

			ps = conn.prepareStatement("insert into RMA_Line_Item_Table(RMA_ID, LineItem_Number, Part_Number, Invoice_Number, Invoice_Date, Quantity, Serial_Number, Reason, Problem_Description, Remarks, Created_On, Updated_On,PO_LineItem_Number,PO_Number,Unit_Price,IS_Exchange,IS_Credit,IS_FA , Status , Close_Date, Receive_Qty, Close_qty, Sales_Order_no) values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");

			Iterator it = list.iterator();
			while(it.hasNext()){
				RMALineItem lineItem = (RMALineItem)it.next();

				ps.setString(1, rmaForm.getRmaId()+"");
				ps.setString(2, lineItem.getLineItemNo()+"");
				ps.setString(3, lineItem.getPartNumber());
				ps.setString(4, lineItem.getInvoiceNumber());
                ps.setDate  (5 , StringUtils.getSQLDate(lineItem.getInvoiceDate(),null));
				ps.setString(6, lineItem.getQuantity()+"");
				ps.setString(7, lineItem.getSerialNumber());
				ps.setString(8, lineItem.getReason());
				ps.setString(9, lineItem.getProblemDescription());
				ps.setString(10, lineItem.getRemarks());
				Date date = Calendar.getInstance().getTime();
				ps.setString(11, StringUtils.formatDateToString ( date , "MM/dd/yyyy" ));
				ps.setString(12, StringUtils.formatDateToString ( date , "MM/dd/yyyy" ));
                ps.setInt(13, lineItem.getPoLineItemNo());
                ps.setString(14 , lineItem.getPoNumber());
                ps.setDouble(15 , lineItem.getUnitPrice());
                ps.setInt(16 , lineItem.getIsExchange()? 0 : 1);
                ps.setInt(17 , lineItem.getIsCredit()? 0 : 1);
                ps.setInt(18 , lineItem.getIsFa()? 0 : 1);
                if (lineItem.getLineItemCloseDate() != null && !"".equalsIgnoreCase(lineItem.getLineItemCloseDate()))
                {
                    status = "CLOSE";
                }
                ps.setString( 19 , status );
                ps.setDate( 20 , StringUtils.getSQLDate(lineItem.getLineItemCloseDate() , null ) );
                ps.setInt( 21 , lineItem.getReceiveQty() );
                ps.setInt( 22 , lineItem.getCloseQty() );
                ps.setString ( 23, lineItem.getSalesOrderNumber() );
                ps.addBatch();
			}
			ps.executeBatch();
		 	success = true;
		}
		catch (Exception se)
        {
			// Exception while updating user profile
			// log it
			se.printStackTrace();

        }
		finally
        {
	    	if (dbTransaction == null) {
		    	try
                {
		    		if (conn != null) {
		        		if (success == true)
		            		conn.commit();
		            	else
		            		conn.rollback();
		       		}
		    	}
		    	catch (SQLException ignore) {}
		 	}

		 	if (ps != null) {
		 		try { ps.close(); } catch (SQLException ignore){}
		 	}

		 	if (conn != null) {
		 		try { conn.close(); } catch (SQLException ignore){}
		 	}
    	}
        return success;
    }

    public static boolean updateRMALineItemDetails(DBTransaction dbTransaction, ChipTrackActionForm form, ArrayList list){

		Connection conn = null;
		CallableStatement cs = null;
		boolean success = false;

		RMAForm rmaForm = (RMAForm)form;
		if((list != null) && list.isEmpty()){
			return false;
		}

		try{
			if(dbTransaction == null){
				conn = DataSource.getConnection();
			}else{
				conn = dbTransaction.getConnection();
			}

			cs = conn.prepareCall("{ call sp_update_rma_line_items (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?) }");

			Iterator it = list.iterator();
			while(it.hasNext()){
				RMALineItem lineItem = (RMALineItem)it.next();

                cs.setInt   (1 , lineItem.getLineItemId());
				cs.setInt   (2 , rmaForm.getRmaId());
				cs.setString(3 , lineItem.getPartNumber());
				cs.setString(4 , lineItem.getInvoiceNumber());
                cs.setDate  (5 , StringUtils.getSQLDate(lineItem.getInvoiceDate(),null));
                cs.setString(6 , lineItem.getSerialNumber());
                cs.setString(7 , lineItem.getProblemDescription());
                cs.setString(8 , lineItem.getReason());
                cs.setString(9 , lineItem.getRemarks());
				cs.setInt   (10, lineItem.getQuantity());
                cs.setInt   (11, lineItem.getPoLineItemNo());
                cs.setString(12 , lineItem.getPoNumber());
                cs.setDouble(13 , lineItem.getUnitPrice());
                cs.setInt   (14 , lineItem.getIsExchange()? 0 : 1);
                cs.setInt   (15 , lineItem.getIsCredit()? 0 : 1);
                cs.setInt   (16 , lineItem.getIsFa()? 0 : 1);
                cs.setDate  (17,  StringUtils.getSQLDate(lineItem.getLineItemCloseDate() , null));
                cs.setInt   (18, lineItem.getCloseQty());
                cs.setInt   (19 , lineItem.getReceiveQty());
                cs.setString(20, lineItem.getSalesOrderNumber());
                cs.addBatch();
			}

			cs.executeBatch();

		 	success = true;
		}
		catch (Exception se) {
			se.printStackTrace();
		}
		finally {
	    	if (dbTransaction == null) {
		    	try {
		    		if (conn != null) {
		        		if (success == true)
		            		conn.commit();
		            	else
		            		conn.rollback();
		       		}
		    	}
		    	catch (SQLException ignore) {}
		 	}

		 	if (cs != null) {
		 		try { cs.close(); } catch (SQLException ignore){}
		 	}

		 	if (conn != null) {
		 		try { conn.close(); } catch (SQLException ignore){}
		 	}
    	}
        return success;
    }


    public static boolean cancelRMALineItemDetails(DBTransaction dbTransaction, ChipTrackActionForm form, ArrayList list)
    {
		Connection conn = null;
		PreparedStatement ps = null;
		boolean success = false;

		RMAForm rmaForm = (RMAForm)form;
		if((list != null) && list.isEmpty())
        {
			return false;
		}
		try{
			if(dbTransaction == null)
            {
				conn = DataSource.getConnection();
			}else
            {
				conn = dbTransaction.getConnection();
			}
			ps = conn.prepareStatement("delete from RMA_Line_Item_Table where RMA_ID = ? and RMA_LineItem_ID = ? ");
			Iterator it = list.iterator();
			while(it.hasNext()){
				RMALineItem lineItem = (RMALineItem)it.next();

                ps.setInt(1, rmaForm.getRmaId());
				ps.setInt(2, lineItem.getLineItemId());

				ps.addBatch();
			}

			ps.executeBatch();

		 	success = true;
		}
		catch (Exception se) {
			se.printStackTrace();
		}
		finally {
	    	if (dbTransaction == null) {
		    	try {
		    		if (conn != null) {
		        		if (success == true)
		            		conn.commit();
		            	else
		            		conn.rollback();
		       		}
		    	}
		    	catch (SQLException ignore) {}
		 	}

		 	if (ps != null) {
		 		try { ps.close(); } catch (SQLException ignore){}
		 	}

		 	if (conn != null) {
		 		try { conn.close(); } catch (SQLException ignore){}
		 	}
    	}
        return success;
    }

    public static void closeRMA(DBTransaction dbTransaction, ChipTrackActionForm form ){

		Connection conn = null;
		CallableStatement cs = null;
		boolean success = false;

		RMAForm rmaForm = (RMAForm)form;

		try{
			if(dbTransaction == null){
				conn = DataSource.getConnection();
			}else{
				conn = dbTransaction.getConnection();
			}

			cs = conn.prepareCall("{ call sp_close_rma_proc (?) }");

            cs.setInt   (1 , rmaForm.getRmaId() );

			cs.executeUpdate();

		 	success = true;
		}
		catch (Exception se) {
			se.printStackTrace();
		}
		finally {
	    	if (dbTransaction == null) {
		    	try {
		    		if (conn != null) {
		        		if (success == true)
		            		conn.commit();
		            	else
		            		conn.rollback();
		       		}
		    	}
		    	catch (SQLException ignore) {}
		 	}

		 	if (cs != null) {
		 		try { cs.close(); } catch (SQLException ignore){}
		 	}

		 	if (conn != null) {
		 		try { conn.close(); } catch (SQLException ignore){}
		 	}
    	}
	}

	public static ArrayList getRmaLineItemDetails(String rmaId){

		Connection conn = null;
		ResultSet rs = null;
		PreparedStatement ps = null;

        int qty = 0;
        double price = 0.0;

		boolean success = false;

		ArrayList list = new ArrayList();
        String isClosed = null;

		try{

			conn = DataSource.getConnection();

			ps = conn.prepareStatement("select RMA_LineItem_ID, RMA_ID, LineItem_Number, Part_Number, Invoice_Number, convert(varchar, Invoice_Date, 101) as Invoice_Date, Quantity, Serial_Number, Reason, Problem_Description, Remarks , PO_LineItem_Number , PO_Number , Unit_Price , IS_Exchange , IS_Credit , IS_FA , Status , convert(varchar, Close_Date , 101) as Close_Date, receive_qty, SALES_ORDER_NO, IS_APPROVED  from RMA_Line_Item_Table where RMA_ID =? order by RMA_LineItem_ID");
			ps.setString(1, rmaId);
			rs = ps.executeQuery();

			while(rs.next()){

				RMALineItem lineItem = new RMALineItem();

				lineItem.setLineItemId(rs.getInt("RMA_LineItem_ID"));
				lineItem.setRmaId(rs.getInt("RMA_ID"));
				lineItem.setLineItemNo(rs.getInt("LineItem_Number"));
				lineItem.setPartNumber(rs.getString("Part_Number"));
				lineItem.setInvoiceNumber(rs.getString("Invoice_Number"));
				lineItem.setInvoiceDate(rs.getString("Invoice_Date"));
				lineItem.setProblemDescription(rs.getString("Problem_Description"));
				lineItem.setRemarks(rs.getString("Remarks"));
                lineItem.setPoLineItemNo(rs.getInt("PO_LineItem_Number"));
                lineItem.setPoNumber(rs.getString("PO_Number"));
                qty = rs.getInt("quantity");
                price = rs.getDouble("Unit_Price");
                lineItem.setQuantity(qty);
                lineItem.setCloseQty(qty);
                lineItem.setUnitPrice(price);
                lineItem.setExtendedPrice(qty * price);
                lineItem.setIsExchange(rs.getInt("IS_Exchange") == 0 ? true : false);
                lineItem.setIsCredit(rs.getInt("IS_Credit") == 0 ? true : false);
                lineItem.setIsFa(rs.getInt("IS_FA") == 0 ? true : false);

                isClosed = rs.getString("status");

                lineItem.setStatus(isClosed);
                lineItem.setIsClosed(isClosed);

                lineItem.setLineItemCloseDate(rs.getString("Close_Date"));
                lineItem.setReceiveQty(rs.getInt("receive_qty"));
                lineItem.setSalesOrderNumber( rs.getString("SALES_ORDER_NO"));
                lineItem.setIsApproved( rs.getInt("IS_APPROVED") == 0 );
                list.add(lineItem);
			}
		 	success = true;
		}
		catch (Exception se) {
			// Exception while updating user profile
			// log it
			se.printStackTrace();
		}
		finally {
	    	try {
		    		if (conn != null) {
		        		if (success == true)
		            		conn.commit();
		            	else
		            		conn.rollback();
		       		}
		    }
		    catch (SQLException ignore) {}


		 	if (ps != null) {
		 		try { ps.close(); } catch (SQLException ignore){}
		 	}

		 	if (conn != null) {
		 		try { conn.close(); } catch (SQLException ignore){}
		 	}
    	}

    	return list;
	}

    public static void updateRmaDetails(DBTransaction dbTransaction, RMAForm form , String status ){

		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;

		boolean success = false;
		try{

			if(dbTransaction == null){
				conn = DataSource.getConnection();
			}else{
				conn = dbTransaction.getConnection();
			}

            ps = conn.prepareStatement("update RMA_Table set RMA_Requested_Date=?, RMA_Contact_Person =?, Phone = ? , Fax = ? , Email = ? , RMA_Issue_Date = ?,  Close_Date = ? , RMA_Status = ? , Comments = ? , Address1 = ? , Address2 = ? , City = ? , State = ? , Zip = ? , Country = ? , Address3 = ? , Address4 = ? , End_User_ID = ? where RMA_ID = ?");
            ps.setDate  (1, StringUtils.getSQLDate( form.getRmaRequestDate() , null));
			ps.setString(2, form.getContactPerson());
            ps.setString(3, form.getPhoneNumber());
            ps.setString(4, form.getFaxNumber());
            ps.setString(5, form.getEmail());
			ps.setDate  (6, StringUtils.getSQLDate( form.getRmaIssueDate() , null));
			ps.setDate  (7, StringUtils.getSQLDate( form.getCloseDate() , null));
            ps.setString(8, status );
            ps.setString(9, form.getComments());
            ps.setString(10, form.getAddress1());
            ps.setString(11, form.getAddress2());
            ps.setString(12, form.getCity());
            ps.setString(13, form.getState());
            ps.setString(14, form.getZipcode());
            ps.setString(15, form.getCountry());
            ps.setString(16, form.getAddress3());
            ps.setString(17, form.getAddress4());
            ps.setInt(18, form.getEndUserID());
            ps.setInt   (19, form.getRmaId());
			ps.executeUpdate();

			success = true;

		}catch(Exception e){
			e.printStackTrace();
		}finally{
			if(dbTransaction == null){
				try{
					if(success == true){
						conn.commit();
					}else{
						conn.rollback();
					}
				}catch(SQLException sql){
				}
			}
			if(ps != null){
				try{ps.close();}catch(Exception e){}
			}
			if(rs != null){
				try{rs.close();}catch(Exception e){}
			}
			if(conn != null){
				try{ conn.close();}catch(Exception e){}
			}
		}
	}
    public static void updateApproveRmaDetails(DBTransaction dbTransaction, RMAForm form , String status ){

		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;

		boolean success = false;
		try{

			if(dbTransaction == null){
				conn = DataSource.getConnection();
			}else{
				conn = dbTransaction.getConnection();
			}

            ps = conn.prepareStatement("update RMA_Table set RMA_Status = ? where RMA_ID = ?");
            ps.setString(1, status );
            ps.setInt   (2, form.getRmaId());
			ps.executeUpdate();
			success = true;
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			if(dbTransaction == null){
				try{
					if(success == true){
						conn.commit();
					}else{
						conn.rollback();
					}
				}catch(SQLException sql){
				}
			}
			if(ps != null){
				try{ps.close();}catch(Exception e){}
			}
			if(rs != null){
				try{rs.close();}catch(Exception e){}
			}
			if(conn != null){
				try{ conn.close();}catch(Exception e){}
			}
		}
	}


    public static void cancelRmaForm(DBTransaction dbTransaction, String rmaId){
        Connection conn = null;
		PreparedStatement ps = null;


		boolean success = false;

		try{

			if(dbTransaction == null){
				conn = DataSource.getConnection();
			}else{
				conn = dbTransaction.getConnection();
			}

            ps = conn.prepareStatement("update RMA_Table set RMA_Status = ? where RMA_ID = ?");
            ps.setString(1, "CANCELLED");
            ps.setString(2, rmaId);

            ps.executeUpdate();

			success = true;

		}catch(Exception e){
			e.printStackTrace();
		}finally{
			if(dbTransaction == null){
				try{
					if(success == true){
						conn.commit();
					}else{
						conn.rollback();
					}
				}catch(SQLException sql){
				}
			}
			if(ps != null){
				try{ps.close();}catch(Exception e){}
			}

			if(conn != null){
				try{ conn.close();}catch(Exception e){}
			}
		}
    }


	public static void updateRmaDetails(DBTransaction dbTransaction, RMAForm form){

		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		boolean success = false;
		try{
			if(dbTransaction == null){

				conn = DataSource.getConnection();
			}else{
				conn = dbTransaction.getConnection();
			}

			ps = conn.prepareStatement("update RMA_Table set RMA_Number=?,  RMA_Contact_Person =?, RMA_Requested_Date=?, RMA_Issue_Date=?, RMA_Status='CLOSE', Close_Date=? from RMA_Table where RMA_ID = ?");
			ps.setString(1, form.getRmaNumber());
			ps.setString(2, form.getContactPerson());
			ps.setString(3, form.getRmaRequestDate());
			ps.setString(4, form.getRmaIssueDate());
			ps.setString(5, form.getCloseDate());
			ps.setString(6, form.getRmaId()+"");

			ps.executeUpdate();

			success = true;

		}catch(Exception e){
			e.printStackTrace();
		}finally{
			if(dbTransaction == null){
				try{
					if(success == true){
						conn.commit();
					}else{
						conn.rollback();
					}
				}catch(SQLException sql){
				}
			}
			if(ps != null){
				try{ps.close();}catch(Exception e){}
			}
			if(rs != null){
				try{rs.close();}catch(Exception e){}
			}
			if(conn != null){
				try{ conn.close();}catch(Exception e){}
			}
		}
	}

    public static boolean isRmaNoExists(DBTransaction dbTransaction , String rmaNo) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        boolean isExists = false;

        try {
            if(dbTransaction == null){
            	conn = DataSource.getConnection();
			}else{
				conn = dbTransaction.getConnection();
			}

            pstmt = conn.prepareStatement ("select RMA_Number from RMA_Table where RMA_Number = ?");
            pstmt.setString (1, rmaNo);
            rs = pstmt.executeQuery();

            if (rs.next() == false) {
                isExists = false;
            }
            else {
        	isExists = true;
            }
        }
        catch (Exception sqlex) {
            sqlex.printStackTrace();
            throw new ScalarException ("Could not get RMA No from database", sqlex);
        }
        finally {
            if (rs != null) {
                try { rs.close(); } catch (SQLException excp) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException excp) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException excp) {}
            }
        }

        return isExists;
   }

    public static String getAutoRMANumber(){

		Connection conn = null;
		CallableStatement cs = null;
		ResultSet rs = null;

        String rmaNumber = null;

		try{

			conn = DataSource.getConnection();

			cs = conn.prepareCall("{ call sp_get_auto_rma_number }");
           	rs = cs.executeQuery();

			while(rs.next()){
                rmaNumber = rs.getString("RMA_NUMBER");
			}


		}catch(Exception e){
			e.printStackTrace();
		}finally{

			if(cs != null){
				try{cs.close();}catch(Exception e){}
			}
			if(rs != null){
				try{rs.close();}catch(Exception e){}
			}
			if(conn != null){
				try{ conn.close();}catch(Exception e){}
			}
		}

		return rmaNumber;
	}

    public static int loadMaxLineItemNo(String rmaId) throws ScalarException
    {
            Connection conn = null;
            PreparedStatement pstmt = null;
            ResultSet rs = null;
            int lineItemNo = 0;

            try {
                conn = DataSource.getConnection();
                pstmt = conn.prepareStatement("select max(LineItem_Number)[MAX_NO]  from RMA_Line_Item_Table where RMA_ID = ?");
                pstmt.setString(1,rmaId);

                rs = pstmt.executeQuery();

                if (rs.next()){
                    lineItemNo = rs.getInt("MAX_NO");
                }

            }
            catch (Exception sqlex) {
                sqlex.printStackTrace();
                throw new ScalarException ("Could not get max lineItem Number for this RMA ", sqlex);
            }
            finally {
                if (rs != null) {
                    try { rs.close(); } catch (SQLException excp) {}
                }
                if (pstmt != null) {
                    try { pstmt.close(); } catch (SQLException excp) {}
                }
                if (conn != null) {
                    try { conn.close(); } catch (SQLException excp) {}
                }
            }

            return lineItemNo + 1;
       }

      public static double getPrice(String poNumber, int poLineItemNo , String partNumber ){

		Connection conn = null;
		CallableStatement cs = null;
		ResultSet rs = null;
        double price = -1;

		try{

			conn = DataSource.getConnection();

			cs = conn.prepareCall("{ call sp_get_price_for_rma_lineitem (?,?,?) }");
			cs.setString(1, poNumber);
			cs.setInt(2, poLineItemNo);
            cs.setString(3,partNumber);

			rs = cs.executeQuery();

			if(rs.next()){
                price = rs.getDouble("Unit_Price");
			}

		}catch(Exception e){
			e.printStackTrace();
		}finally{

			if(cs != null){
				try{cs.close();}catch(Exception e){}
			}
			if(rs != null){
				try{rs.close();}catch(Exception e){}
			}
			if(conn != null){
				try{ conn.close();}catch(Exception e){}
			}
		}

		return price;
	}


}